﻿using log4net;
using SISCON.Model;
using SISCON.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace SISCON.Dao
{
    public class ScheduleDAO : CommonDAO
    {
        private static ILog log = LogManager.GetLogger(typeof(ScheduleDAO));

        /// <summary>
        /// Obtiene un schedule de la base de datos especificado por scheduleId
        /// </summary>
        /// <param name="scheduleId">Id del schedule deseado</param>
        /// <returns>Schedule</returns>
        public Schedule GetSchedule(long scheduleId)
        {
            SqlCommand sqlCommand = new SqlCommand();
            SqlDataReader reader = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);

                sqlCommand.CommandText = "SELECT * FROM sis_schedule ss WHERE ss.schedule_id = @scheduleId";
                sqlCommand.Connection = conn;
                sqlCommand.Parameters.Add(getNewParameter("@scheduleId", SqlDbType.BigInt, 0, scheduleId));
                sqlCommand.Prepare();

                conn.Open();

                reader = sqlCommand.ExecuteReader();

                Schedule schedule = null;

                if (reader.Read())
                {
                    schedule = new Schedule();
                    schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                    schedule.Days = getReaderCommaSeparatedString(reader, "days");
                    schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                    schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                    schedule.Classroom = new Classroom()
                    {
                        ClassroomLocation = getReaderString(reader, "classroom_location"),
                        ClassroomNumber = getReaderString(reader, "classroom_number")
                    };
                }

                return schedule;
            }
            catch (Exception exp)
            {
                log.Error("Error in GetSchedule()", exp);
                throw exp;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }

        /// <summary>
        /// Obtiene todos los schedules almacenados en la base de datos
        /// </summary>
        /// <returns>Lista de schedules</returns>
        public List<Schedule> GetAllSchedules()
        {
            SqlCommand sqlCommand = new SqlCommand();
            SqlDataReader reader = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);

                sqlCommand.CommandText = "SELECT * FROM sis_schedule";
                sqlCommand.Connection = conn;
                conn.Open();

                reader = sqlCommand.ExecuteReader();

                List<Schedule> schedules = null;

                while (reader.Read())
                {
                    Schedule schedule = new Schedule();

                    if (schedules == null)
                    {
                        schedules = new List<Schedule>();
                    }

                    schedule = new Schedule();
                    schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                    schedule.Days = getReaderCommaSeparatedString(reader, "days");
                    schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                    schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                    schedule.Classroom = new Classroom()
                    {
                        ClassroomLocation = getReaderString(reader, "classroom_location"),
                        ClassroomNumber = getReaderString(reader, "classroom_number")
                    };

                    schedules.Add(schedule);
                }

                return schedules;
            }
            catch (Exception exp)
            {
                log.Error("Error in GetAllSchedules()", exp);
                throw exp;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }

        /// <summary>
        /// Guarda un nuevo schedule dentro de la base de datos
        /// </summary>
        /// <param name="scheduleId">Id del shcedule deseado</param>
        /// <param name="days">Dias del schedule</param>
        /// <param name="startsAt">Fecha en la que comienza el schedule</param>
        /// <param name="endsAt">Fecha en la que termina el schedule</param>
        /// <param name="classroomLocation">Ubicacion del aula del schedule</param>
        /// <param name="classroomNumber">Codigo del aula del schedule</param>
        /// <returns></returns>
        public long SaveSchedule(string scheduleId, List<string> days, DateTime startsAt, DateTime endsAt, string classroomLocation, string classroomNumber, long createdBy)
        {
            SqlConnection conn = null;
            SqlTransaction trx = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                trx = conn.BeginTransaction();
                SqlCommand sqlCommand = new SqlCommand(null, conn, trx);

                if (scheduleId == null)
                {
                    sqlCommand.CommandText = " INSERT INTO sis_schedule(days, starts_at, ends_at, classroom_location, classroom_number, created_by) VALUES "+
                                             " (@days, @startsAt, @endsAt, @classroomLocation, @classroomNumber, @createdBy); " +                     
                                             " SELECT SCOPE_IDENTITY(); ";
                    sqlCommand.Parameters.Add(getNewParameter("@days", SqlDbType.NVarChar, 255, String.Join(",", days.ToArray())));
                    sqlCommand.Parameters.Add(getNewParameter("@startsAt", SqlDbType.DateTime, 0, startsAt));
                    sqlCommand.Parameters.Add(getNewParameter("@endsAt", SqlDbType.DateTime, 0, endsAt));
                    sqlCommand.Parameters.Add(getNewParameter("@classroomLocation", SqlDbType.NVarChar, 50, classroomLocation));
                    sqlCommand.Parameters.Add(getNewParameter("@classroomNumber", SqlDbType.NVarChar, 50, classroomNumber));
                    sqlCommand.Parameters.Add(getNewParameter("@createdBy", SqlDbType.BigInt, 0, createdBy));

                    sqlCommand.Prepare();

                    scheduleId = sqlCommand.ExecuteScalar().ToString();
                }
                else
                {
                    sqlCommand.CommandText = " UPDATE sis_schedule SET days = @days, starts_at = @startsAt, ends_at = @endsAt " +
                                             " , classroom_location = @classroomLocation, classroom_number = @classroomNumber, created_by = @createdBy " +
                                             " WHERE schedule_id = @scheduleId ";

                    sqlCommand.Parameters.Add(getNewParameter("@scheduleid", SqlDbType.BigInt, 0, long.Parse(scheduleId)));
                    sqlCommand.Parameters.Add(getNewParameter("@days", SqlDbType.NVarChar, 255, String.Join(",", days.ToArray())));
                    sqlCommand.Parameters.Add(getNewParameter("@startsAt", SqlDbType.DateTime, 0, startsAt));
                    sqlCommand.Parameters.Add(getNewParameter("@endsAt", SqlDbType.DateTime, 0, endsAt));
                    sqlCommand.Parameters.Add(getNewParameter("@classroomLocation", SqlDbType.NVarChar, 50, classroomLocation));
                    sqlCommand.Parameters.Add(getNewParameter("@classroomNumber", SqlDbType.NVarChar, 50, classroomNumber));
                    sqlCommand.Parameters.Add(getNewParameter("@createdBy", SqlDbType.BigInt, 0, createdBy));

                    sqlCommand.Prepare();
                    sqlCommand.ExecuteNonQuery();
                }

                trx.Commit();
            }
            catch (Exception exp)
            {
                log.Error("Error in SaveSchedule()", exp);
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return long.Parse(scheduleId);
        }

        /// <summary>
        /// Elimina un schedule de la base de datos especificado por el scheduleId
        /// </summary>
        /// <param name="scheduleId">Id interno del schedule</param>
        public void DeleteSchedule(string scheduleId)
        {
            SqlConnection conn = null;
            SqlTransaction trx = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                trx = conn.BeginTransaction();

                SqlCommand sqlCommand = new SqlCommand(null, conn, trx);
                sqlCommand.CommandText = " delete from sis_schedule where schedule_id = @scheduleId";
                sqlCommand.Parameters.Add(getNewParameter("@scheduleId", SqlDbType.BigInt, 0, scheduleId));
                sqlCommand.ExecuteNonQuery();

                trx.Commit();
            }
            catch (SISCONException ex)
            {
                throw ex;
            }
            catch (Exception exp)
            {
                log.Error("Error in DeleteSchedule()", exp);
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
        
        /// <summary>
        /// Alamcena una nueva instancia de la tabla courseSchedule
        /// </summary>
        /// <param name="courseId">Id interno del curso</param>
        /// <param name="scheduleId">Id interno del schedule</param>
        public void SaveCourseSchedule(long courseId, long scheduleId)
        {
            SqlCommand sqlCommand = null;
            SqlConnection conn = null;
            SqlTransaction trx = null;

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                trx = conn.BeginTransaction();

                sqlCommand = new SqlCommand(null, conn, trx);
                sqlCommand.CommandText = "INSERT INTO sis_course_schedule (course_id, schedule_id) VALUES (@courseId, @scheduleId)";
                sqlCommand.Parameters.Add(getNewParameter("@courseId", SqlDbType.BigInt, 0, courseId));
                sqlCommand.Parameters.Add(getNewParameter("@scheduleId", SqlDbType.BigInt, 0, scheduleId));

                sqlCommand.Prepare();
                sqlCommand.ExecuteNonQuery();

                trx.Commit();
            }
            catch (Exception ex)
            {
                log.Error("Error in SaveCourseSchedule()", ex);
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}